package com.exam.dao;

import com.exam.domain.entity.Answer;
import com.exam.domain.entity.Exam;
import com.exam.domain.entity.Record;
import com.exam.domain.entity.Student;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {


    public Student getStudentInfoByStudentId(Integer studentId) {
        String sql = "SELECT * FROM student_infos WHERE student_id = ?";
        // 执行查询操作，返回结果集
        // 执行查询操作，返回 ResultSet
        try {
            Connection connection = DBUtil.getConnection();
            ResultSet rs = DBUtil.query(connection, sql, studentId);
            if (rs.next()) {
                // 结果集中有数据，映射到 User 对象
                Student student = new Student();
                student.setStudentId(rs.getInt("student_id"));
                student.setStudentName(rs.getString("student_name"));
                student.setStudentNo(rs.getString("student_no"));
                student.setStudentMajor(rs.getString("student_major"));
                // 返回查询到的用户
                return student;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("查询学生信息失败", e);
        }
        // 如果没有找到用户，返回 null
        return null;
    }

    public List<Exam> getExamListWhichActive() {
        String sql1 = "SELECT * FROM exams WHERE status = 'active' order by created_at desc";
        // 执行查询操作，返回结果集
        // 执行查询操作，返回 ResultSet
        List<Exam> exams = new ArrayList<>();
        try {
            Connection connection = DBUtil.getConnection();
            ResultSet rs = DBUtil.query(connection, sql1);
            while (rs.next()) {
                // created_by, question_text, correct_option, option_a, option_b, option_c, option_d
                // 结果集中有数据，映射到 User 对象
                Exam exam = new Exam();
                exam.setExamId(rs.getInt("exam_id"));
                Integer teacherId = rs.getInt("created_by");
                exam.setCreatedBy(teacherId);
                exam.setTitle(rs.getString("title"));
                exam.setQuestionCount(rs.getInt("question_count"));
                exam.setScorePerQuestion(rs.getInt("score_per_question"));
                exam.setCreatedAt(rs.getTimestamp("created_at"));
                exam.setStatus(rs.getString("status"));

                // 再次查询，老师的名字
                String sql2 = "SELECT * FROM teacher_infos WHERE teacher_id = ?";
                // 使用新的连接进行查询
                try (Connection teacherConnection = DBUtil.getConnection();
                     ResultSet teacherRs = DBUtil.query(teacherConnection, sql2, teacherId)) {

                    if (teacherRs.next()) {
                        String teacherName = teacherRs.getString("teacher_name");
                        exam.setTeacherName(teacherName);
                    }
                }

                exams.add(exam);
            }
            return exams;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("查询试卷失败", e);
        }
    }


    // 提交试卷记录并保存学生答案
    public void submitExamRecordAndStudentAnswer(Record record, List<Answer> answers) {
        Connection connection = null;

        try {
            // 获取数据库连接并开启事务
            connection = DBUtil.getConnection();
            DBUtil.beginTransaction(connection);

            // 1. 插入考试记录表
            String insertExamRecordSql = "INSERT INTO exam_records (student_id, exam_id, total_score) " +
                    "VALUES (?, ?, ?)";
            DBUtil.update(connection, insertExamRecordSql,
                    record.getStudentId(), record.getExamId(), record.getTotalScore());

            // 2. 获取刚插入的 record_id（假设数据库支持返回生成的主键）
            ResultSet rs = DBUtil.query(connection, "SELECT LAST_INSERT_ID()");
            int recordId = 0;
            if (rs.next()) {
                recordId = rs.getInt(1);
            }
            rs.close();

            // 3. 插入学生答案到 student_answers 表
            String insertStudentAnswerSql = "INSERT INTO student_answers (record_id, question_id, selected_option, score) VALUES (?, ?, ?, ?)";
            for (Answer answer : answers) {
                DBUtil.update(connection, insertStudentAnswerSql, recordId, answer.getQuestionId(), answer.getSelectedOption(), answer.getScore());
            }

            // 提交事务
            DBUtil.commitTransaction(connection);
            System.out.println("提交试卷成功");

        } catch (Exception e) {
            // 异常时回滚事务
            try {
                if (connection != null) {
                    DBUtil.rollbackTransaction(connection);
                }
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
            e.printStackTrace();
            throw new RuntimeException("提交试卷失败", e);
        } finally {
            // 关闭数据库连接
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException closeEx) {
                closeEx.printStackTrace();
            }
        }
    }
}
